package chatuidemo.db;

import android.content.ContentValues;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.net.Uri;
import android.text.TextUtils;

import com.ysm.smfu.bean.Friend;
import com.ysm.smfu.bean.Msg;
import com.ysm.smfu.bean.SysMsg;
import com.ysm.smfu.contans.SpKey;
import com.ysm.smfu.utils.PrintUtil;
import com.ysm.smfu.utils.UIUtils;
import com.ysm.smfu.welcome.SharedConfig;
import com.hyphenate.util.HanziToPinyin;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;

import chatuidemo.Constant;
import chatuidemo.domain.InviteMessage;
import chatuidemo.domain.RobotUser;
import easeui.domain.EaseUser;
import easeui.utils.EaseCommonUtils;


public class DBManager {
    static private DBManager dbMgr = new DBManager();
    private DbOpenHelper dbHelper;

    private DBManager() {
        dbHelper = DbOpenHelper.getInstance(UIUtils.getContext());
    }

    public static synchronized DBManager getInstance() {
        if (dbMgr == null) {
            dbMgr = new DBManager();
        }
        return dbMgr;
    }

    private Uri uri = Uri.parse("content://dmh.contact");

    /**
     * 保存联系人列表
     *
     * @param contactList
     */
    synchronized public void saveContactList2(List<Friend> contactList) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.delete("contactlist", null, null);
            contactList.add(0, getMy());
            for (Friend friend : contactList) {
                if (friend.hxId == null)
                    continue;
                ContentValues values = new ContentValues();
                values.put("userId", friend.id);
                if (friend.petName != null)
                    values.put("petname", friend.petName);
                if (friend.imageAddress != null && !friend.imageAddress.startsWith("null?"))
                    values.put("imageAddress", friend.imageAddress);
                if (friend.hxId != null) {
                    values.put("hxId", friend.hxId);
                }
                if (friend.sex != null) {
                    if (TextUtils.equals(friend.sex, "1"))
                        values.put("sex", "男");
                    else if (TextUtils.equals(friend.sex, "0"))
                        values.put("sex", "女");
                    else
                        values.put("sex", friend.sex);
                }
                if (friend.birthday != null)
                    values.put("birthday", friend.birthday);
                if (friend.address != null)
                    values.put("address", friend.address);
                if (friend.phone != null)
                    values.put("phone", friend.phone);
                if (friend.is_business != null)
                    values.put("is_business", friend.is_business);
                if (friend.lastloginTime != null)
                    values.put("lastloginTime", friend.lastloginTime);
                db.replace("contactlist", null, values);
            }
//            防止保存之后原数据发生变化
            contactList.remove(0);
            PrintUtil.printRespones("保存到数据库的数据：", contactList.toString());
            //            主动添加好友时，需要时刻获取好友变化
            UIUtils.getContext().getContentResolver().notifyChange(uri, null);

        }
    }

    /**
     * 清空联系人
     */
    synchronized public void clearContactList() {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.delete("contactlist", null, null);
        }
    }

    /**
     * 获取用户信息
     *
     * @return
     */
    private Friend getMy() {
        String name = new SharedConfig(UIUtils.getContext()).getCommoneSp().getString(SpKey.NAME, "");
        SharedPreferences currShared = new SharedConfig(UIUtils.getContext(), name).getPrivateSp();
        String userId = currShared.getString(SpKey.USERID, "");
        String petNmae = currShared.getString(SpKey.NC, "");
        String imageAddress = currShared.getString(SpKey.IMAGEADDRESS, "");
        String hxId = currShared.getString(SpKey.HXID, "");
        String sex = currShared.getString(SpKey.SEX, "");
        String birthday = currShared.getString(SpKey.SR, "");
        String address = currShared.getString(SpKey.ADDRESS, "");
        String phone = currShared.getString(SpKey.NAME, "");
        String is_business = currShared.getString(SpKey.IS_BUSINESS, "");
        String lastloginTime = currShared.getString(SpKey.LAST_TIME, "");
        Friend friend = new Friend();
        friend.id = userId;
        friend.petName = petNmae;
        friend.imageAddress = imageAddress;
        friend.hxId = hxId;
        friend.sex = sex;
        friend.birthday = birthday;
        friend.address = address;
        friend.phone = phone;
        friend.is_business = is_business;
        friend.lastloginTime = lastloginTime;
        PrintUtil.printRespones("我", friend.toString());
        return friend;
    }

    /**
     * 获取联系人列表
     *
     * @return
     */
    synchronized public Map<String, Friend> getContactList2() {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Map<String, Friend> friends = new Hashtable<String, Friend>();
        if (db.isOpen()) {
            Cursor cursor = db.rawQuery("select * from contactlist", null);
//            下移一位，过滤掉用户自身
            cursor.moveToNext();
            while (cursor.moveToNext()) {
                String userId = cursor.getString(cursor.getColumnIndex("userId"));
                String nick = cursor.getString(cursor.getColumnIndex("petname"));
                String avatar = cursor.getString(cursor.getColumnIndex("imageAddress"));
                String sex = cursor.getString(cursor.getColumnIndex("sex"));
                String birthday = cursor.getString(cursor.getColumnIndex("birthday"));
                String address = cursor.getString(cursor.getColumnIndex("address"));
                String phone = cursor.getString(cursor.getColumnIndex("phone"));
                String is_business = cursor.getString(cursor.getColumnIndex("is_business"));
                String lastloginTime = cursor.getString(cursor.getColumnIndex("lastloginTime"));
                String hxId = cursor.getString(cursor.getColumnIndex("hxId"));
                Friend friend = new Friend();
                friend.id = userId;
                friend.petName = nick;
                friend.imageAddress = avatar;
                friend.sex = sex;
                friend.birthday = birthday;
                friend.address = address;
                friend.phone = phone;
                friend.is_business = is_business;
                friend.lastloginTime = lastloginTime;
                // hxId有可能为null？？
                if (hxId == null)
                    hxId = "";
                friend.hxId = hxId;
                friends.put(hxId, friend);
            }
            cursor.close();
        }
        return friends;
    }

    /**
     * 没有过滤掉自身
     *
     * @return
     */
    synchronized public Map<String, Friend> getContactList3() {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Map<String, Friend> friends = new Hashtable<String, Friend>();
        if (db.isOpen()) {
            Cursor cursor = db.rawQuery("select * from contactlist", null);
            while (cursor.moveToNext()) {
                String userId = cursor.getString(cursor.getColumnIndex("userId"));
                String nick = cursor.getString(cursor.getColumnIndex("petname"));
                String imageAddress = cursor.getString(cursor.getColumnIndex("imageAddress"));
                String sex = cursor.getString(cursor.getColumnIndex("sex"));
                String birthday = cursor.getString(cursor.getColumnIndex("birthday"));
                String address = cursor.getString(cursor.getColumnIndex("address"));
                String phone = cursor.getString(cursor.getColumnIndex("phone"));
                String is_business = cursor.getString(cursor.getColumnIndex("is_business"));
                String lastloginTime = cursor.getString(cursor.getColumnIndex("lastloginTime"));
                String hxId = cursor.getString(cursor.getColumnIndex("hxId"));
                Friend friend = new Friend();
                friend.id = userId;
                friend.petName = nick;
                friend.imageAddress = imageAddress;
                friend.sex = sex;
                friend.birthday = birthday;
                friend.address = address;
                friend.phone = phone;
                friend.is_business = is_business;
                friend.lastloginTime = lastloginTime;
                PrintUtil.printRespones("获取头像", friend.imageAddress);
                // hxId为什么会为null？？
                friend.hxId = hxId;
                if (hxId != null)
                    friends.put(hxId, friend);
            }
            cursor.close();
        }
        return friends;
    }

    /**
     * 删除指定联系人
     *
     * @param userId
     */
    synchronized public void deleteContact2(String userId) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.delete("contactlist", "userId" + " = ?", new String[]{userId});
//            通知观察者更新数据
            UIUtils.getContext().getContentResolver().notifyChange(uri, null);
        }
    }

    /**
     * 插入一个联系人
     *
     * @param friend
     */
    synchronized public void saveContact2(Friend friend) {
        if (friend.hxId == null)
            return;
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("userId", friend.id);
        if (friend.petName != null)
            values.put("petname", friend.petName);
        if (friend.imageAddress != null && !friend.imageAddress.startsWith("null?")) {
            values.put("imageAddress", friend.imageAddress);
        }
        values.put("hxId", friend.hxId);
        if (friend.sex != null) {
            if (TextUtils.equals(friend.sex, "1"))
                values.put("sex", "男");
            else if (TextUtils.equals(friend.sex, "0"))
                values.put("sex", "女");
            else
                values.put("sex", friend.sex);
        }
        if (friend.birthday != null)
            values.put("birthday", friend.birthday);
        if (friend.address != null)
            values.put("address", friend.address);
        if (friend.phone != null)
            values.put("phone", friend.phone);
        if (friend.is_business != null)
            values.put("is_business", friend.is_business);
        if (friend.lastloginTime != null)
            values.put("lastloginTime", friend.lastloginTime);
        if (db.isOpen()) {
            db.update("contactlist", values, "hxId=?", new String[]{friend.hxId});
//            db.replace("contactlist", null, values);
            UIUtils.getContext().getContentResolver().notifyChange(uri, null);
            PrintUtil.printRespones("包括自身在内的全部好友",getContactList3().values().toString());
        }
    }

    /**
     * 通过环信ID获取用户信息
     *
     * @param hxId
     * @return
     */
    synchronized public Friend getFriendByHxId(String hxId) {
        Map<String, Friend> contactList2 = getContactList3();
        PrintUtil.printRespones("通过环信ID获取用户信息",contactList2.toString());
        return contactList2.get(hxId);
    }

    private Uri uri2 = Uri.parse("content://dmh.sq_contact");

    /**
     * 保存申请列表
     *
     * @param contactList
     */
    synchronized public void saveShenQingContact(List<Friend> contactList) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.delete("shenqingcontact", null, null);
            for (Friend friend : contactList) {
                ContentValues values = new ContentValues();
                values.put("userId", friend.id);
                if (friend.petName != null)
                    values.put("petname", friend.petName);
                if (friend.imageAddress != null && !friend.imageAddress.startsWith("null?"))
                    values.put("imageAddress", friend.imageAddress);
                values.put("hxId", friend.hxId);
                if (friend.sex != null)
                    values.put("sex", friend.sex);
                if (friend.birthday != null)
                    values.put("birthday", friend.birthday);
                if (friend.address != null)
                    values.put("address", friend.address);
                if (friend.phone != null)
                    values.put("phone", friend.phone);
                if (friend.is_business != null)
                    values.put("is_business", friend.is_business);
                if (friend.lastloginTime != null)
                    values.put("lastloginTime", friend.lastloginTime);
                if (friend.applyState != null)
                    values.put("applyState", friend.applyState);
                db.replace("shenqingcontact", null, values);
                UIUtils.getContext().getContentResolver().notifyChange(uri2, null);
            }
        }
    }

    /**
     * 获取申请人列表
     *
     * @return
     */
    synchronized public Map<String, List<Friend>> getShenqingContact() {
        Map<String, List<Friend>> map = new Hashtable<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        List<Friend> no = new ArrayList<>();
        List<Friend> has = new ArrayList<>();
        if (db.isOpen()) {
            Cursor cursor = db.rawQuery("select * from shenqingcontact", null);
            while (cursor.moveToNext()) {
                String userId = cursor.getString(cursor.getColumnIndex("userId"));
                String nick = cursor.getString(cursor.getColumnIndex("petname"));
                String avatar = cursor.getString(cursor.getColumnIndex("imageAddress"));
                String sex = cursor.getString(cursor.getColumnIndex("sex"));
                String birthday = cursor.getString(cursor.getColumnIndex("birthday"));
                String address = cursor.getString(cursor.getColumnIndex("address"));
                String phone = cursor.getString(cursor.getColumnIndex("phone"));
                String is_business = cursor.getString(cursor.getColumnIndex("is_business"));
                String lastloginTime = cursor.getString(cursor.getColumnIndex("lastloginTime"));
                String hxId = cursor.getString(cursor.getColumnIndex("hxId"));
                String applyState = cursor.getString(cursor.getColumnIndex("applyState"));
                Friend friend = new Friend();
                friend.id = userId;
                friend.petName = nick;
                friend.imageAddress = avatar;
                friend.sex = sex;
                friend.birthday = birthday;
                friend.address = address;
                friend.phone = phone;
                friend.is_business = is_business;
                friend.lastloginTime = lastloginTime;
                friend.hxId = hxId;
                friend.applyState = applyState;
                if (TextUtils.equals(applyState, "1")) {
                    has.add(friend);
                } else {
                    no.add(friend);
                }
            }
            map.put("has", has);
            map.put("no", no);
            cursor.close();
        }
        return map;
    }

    /**
     * 删除申请消息
     *
     * @param userId
     */
    synchronized public void deleteShenqingContact(String userId) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.delete("shenqingcontact", "userId" + " = ?", new String[]{userId});
            //            通知观察者更新数据
            UIUtils.getContext().getContentResolver().notifyChange(uri2, null);
        }
    }

    /**
     * 插入一条申请消息
     *
     * @param friend
     */
    synchronized public void insertShenqingContact(Friend friend) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("userId", friend.id);
        if (friend.petName != null)
            values.put("petname", friend.petName);
        if (friend.imageAddress != null && !friend.imageAddress.startsWith("null?"))
            values.put("imageAddress", friend.imageAddress);
        values.put("hxId", friend.hxId);
        if (friend.sex != null)
            values.put("sex", friend.sex);
        if (friend.birthday != null)
            values.put("birthday", friend.birthday);
        if (friend.address != null)
            values.put("address", friend.address);
        if (friend.phone != null)
            values.put("phone", friend.phone);
        if (friend.is_business != null)
            values.put("is_business", friend.is_business);
        if (friend.lastloginTime != null)
            values.put("lastloginTime", friend.lastloginTime);
        if (friend.applyState != null)
            values.put("applyState", friend.applyState);
        if (db.isOpen()) {
            db.replace("shenqingcontact", null, values);
            //            通知观察者更新数据
            UIUtils.getContext().getContentResolver().notifyChange(uri2, null);
        }
    }

    /**
     * 更新申请消息为已添加
     *
     * @param
     * @param userId
     */
    synchronized public void updateShenqingContact(String userId) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("applyState", "1");
        db.update("shenqingcontact", contentValues, "userId=?", new String[]{userId});
        UIUtils.getContext().getContentResolver().notifyChange(uri2, null);
    }

    synchronized public void saveContactList(List<EaseUser> contactList) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.delete(UserDao.TABLE_NAME, null, null);
            for (EaseUser user : contactList) {
                ContentValues values = new ContentValues();
                values.put(UserDao.COLUMN_NAME_ID, user.getUsername());
                if (user.getNick() != null)
                    values.put(UserDao.COLUMN_NAME_NICK, user.getNick());
                if (user.getAvatar() != null)
                    values.put(UserDao.COLUMN_NAME_AVATAR, user.getAvatar());
                db.replace(UserDao.TABLE_NAME, null, values);
            }
        }
    }

    Uri uri3 = Uri.parse("content://dmh.sys_msg");

    /**
     * 保存系统消息列表
     */
    synchronized public void saveSysMsg(SysMsg sysMsg) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.delete("sysMsg", null, null);
            for (Msg msg : sysMsg.message) {
                ContentValues values = new ContentValues();
                values.put("userId", msg.userId);
                if (msg.id != null)
                    values.put("id", msg.id);
                if (msg.syMessage != null)
                    values.put("syMessage", msg.syMessage);
                if (msg.message_time != null) {
                    values.put("message_time", msg.message_time);
                }
                if (msg.messageState != null)
                    values.put("messageState", msg.messageState);
                if (msg.read_time != null)
                    values.put("read_time", msg.read_time);
                if (msg.messageId != null)
                    values.put("messageId", msg.messageId);
                db.replace("sysMsg", null, values);
            }
            //            主动添加好友时，需要时刻获取好友变化
            UIUtils.getContext().getContentResolver().notifyChange(uri3, null);
        }
    }


    /**
     * 获取系统消息列表
     */
    /**
     * 获取联系人列表
     *
     * @return
     */
    synchronized public SysMsg getSysMsg() {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        SysMsg sysMsg = new SysMsg();
        List<Msg> message = new ArrayList<>();
        if (db.isOpen()) {
            Cursor cursor = db.rawQuery("select * from sysMsg", null);
            while (cursor.moveToNext()) {
                String userId = cursor.getString(cursor.getColumnIndex("userId"));
                String syMessage = cursor.getString(cursor.getColumnIndex("syMessage"));
                String message_time = cursor.getString(cursor.getColumnIndex("message_time"));
                String messageState = cursor.getString(cursor.getColumnIndex("messageState"));
                String read_time = cursor.getString(cursor.getColumnIndex("read_time"));
                String id = cursor.getString(cursor.getColumnIndex("id"));
                String messageId = cursor.getString(cursor.getColumnIndex("messageId"));
                Msg ms = new Msg();
                ms.userId = userId;
                ms.syMessage = syMessage;
                ms.message_time = message_time;
                ms.messageState = messageState;
                ms.read_time = read_time;
                ms.id = id;
                ms.messageId = messageId;
                message.add(ms);
            }
            sysMsg.message = message;
            cursor.close();
        }
        return sysMsg;
    }

    /**
     * get contact list
     *
     * @return
     */
    synchronized public Map<String, EaseUser> getContactList() {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Map<String, EaseUser> users = new Hashtable<String, EaseUser>();
        if (db.isOpen()) {
            Cursor cursor = db.rawQuery("select * from " + UserDao.TABLE_NAME /* + " desc" */, null);
            while (cursor.moveToNext()) {
                String username = cursor.getString(cursor.getColumnIndex(UserDao.COLUMN_NAME_ID));
                String nick = cursor.getString(cursor.getColumnIndex(UserDao.COLUMN_NAME_NICK));
                String avatar = cursor.getString(cursor.getColumnIndex(UserDao.COLUMN_NAME_AVATAR));
                EaseUser user = new EaseUser(username);
                user.setNick(nick);
                user.setAvatar(avatar);
                if (username.equals(Constant.NEW_FRIENDS_USERNAME) || username.equals(Constant.GROUP_USERNAME)
                        || username.equals(Constant.CHAT_ROOM) || username.equals(Constant.CHAT_ROBOT)) {
                    user.setInitialLetter("");
                } else {
                    EaseCommonUtils.setUserInitialLetter(user);
                }
                users.put(username, user);
            }
            cursor.close();
        }
        return users;
    }

    /**
     * delete a contact
     *
     * @param username
     */
    synchronized public void deleteContact(String username) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.delete(UserDao.TABLE_NAME, UserDao.COLUMN_NAME_ID + " = ?", new String[]{username});
        }
    }

    /**
     * save a contact
     *
     * @param user
     */
    synchronized public void saveContact(EaseUser user) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(UserDao.COLUMN_NAME_ID, user.getUsername());
        if (user.getNick() != null)
            values.put(UserDao.COLUMN_NAME_NICK, user.getNick());
        if (user.getAvatar() != null)
            values.put(UserDao.COLUMN_NAME_AVATAR, user.getAvatar());
        if (db.isOpen()) {
            db.replace(UserDao.TABLE_NAME, null, values);
        }
    }

    public void setDisabledGroups(List<String> groups) {
        setList(UserDao.COLUMN_NAME_DISABLED_GROUPS, groups);
    }

    public List<String> getDisabledGroups() {
        return getList(UserDao.COLUMN_NAME_DISABLED_GROUPS);
    }

    public void setDisabledIds(List<String> ids) {
        setList(UserDao.COLUMN_NAME_DISABLED_IDS, ids);
    }

    public List<String> getDisabledIds() {
        return getList(UserDao.COLUMN_NAME_DISABLED_IDS);
    }

    synchronized private void setList(String column, List<String> strList) {
        StringBuilder strBuilder = new StringBuilder();

        for (String hxid : strList) {
            strBuilder.append(hxid).append("$");
        }

        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            ContentValues values = new ContentValues();
            values.put(column, strBuilder.toString());

            db.update(UserDao.PREF_TABLE_NAME, values, null, null);
        }
    }

    synchronized private List<String> getList(String column) {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select " + column + " from " + UserDao.PREF_TABLE_NAME, null);
        if (!cursor.moveToFirst()) {
            cursor.close();
            return null;
        }

        String strVal = cursor.getString(0);
        if (strVal == null || strVal.equals("")) {
            return null;
        }

        cursor.close();

        String[] array = strVal.split("$");

        if (array.length > 0) {
            List<String> list = new ArrayList<String>();
            Collections.addAll(list, array);
            return list;
        }

        return null;
    }

    /**
     * save a message
     *
     * @param message
     * @return return cursor of the message
     */
    public synchronized Integer saveMessage(InviteMessage message) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        int id = -1;
        if (db.isOpen()) {
            ContentValues values = new ContentValues();
            values.put(InviteMessgeDao.COLUMN_NAME_FROM, message.getFrom());
            values.put(InviteMessgeDao.COLUMN_NAME_GROUP_ID, message.getGroupId());
            values.put(InviteMessgeDao.COLUMN_NAME_GROUP_Name, message.getGroupName());
            values.put(InviteMessgeDao.COLUMN_NAME_REASON, message.getReason());
            values.put(InviteMessgeDao.COLUMN_NAME_TIME, message.getTime());
            values.put(InviteMessgeDao.COLUMN_NAME_STATUS, message.getStatus().ordinal());
            values.put(InviteMessgeDao.COLUMN_NAME_GROUPINVITER, message.getGroupInviter());
            db.insert(InviteMessgeDao.TABLE_NAME, null, values);

            Cursor cursor = db.rawQuery("select last_insert_rowid() from " + InviteMessgeDao.TABLE_NAME, null);
            if (cursor.moveToFirst()) {
                id = cursor.getInt(0);
            }
            cursor.close();
        }
        return id;
    }

    /**
     * update message
     *
     * @param msgId
     * @param values
     */
    synchronized public void updateMessage(int msgId, ContentValues values) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.update(InviteMessgeDao.TABLE_NAME, values, InviteMessgeDao.COLUMN_NAME_ID + " = ?", new String[]{String.valueOf(msgId)});
        }
    }

    /**
     * get messges
     *
     * @return
     */
    synchronized public List<InviteMessage> getMessagesList() {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        List<InviteMessage> msgs = new ArrayList<InviteMessage>();
        if (db.isOpen()) {
            Cursor cursor = db.rawQuery("select * from " + InviteMessgeDao.TABLE_NAME + " desc", null);
            while (cursor.moveToNext()) {
                InviteMessage msg = new InviteMessage();
                int id = cursor.getInt(cursor.getColumnIndex(InviteMessgeDao.COLUMN_NAME_ID));
                String from = cursor.getString(cursor.getColumnIndex(InviteMessgeDao.COLUMN_NAME_FROM));
                String groupid = cursor.getString(cursor.getColumnIndex(InviteMessgeDao.COLUMN_NAME_GROUP_ID));
                String groupname = cursor.getString(cursor.getColumnIndex(InviteMessgeDao.COLUMN_NAME_GROUP_Name));
                String reason = cursor.getString(cursor.getColumnIndex(InviteMessgeDao.COLUMN_NAME_REASON));
                long time = cursor.getLong(cursor.getColumnIndex(InviteMessgeDao.COLUMN_NAME_TIME));
                int status = cursor.getInt(cursor.getColumnIndex(InviteMessgeDao.COLUMN_NAME_STATUS));
                String groupInviter = cursor.getString(cursor.getColumnIndex(InviteMessgeDao.COLUMN_NAME_GROUPINVITER));

                msg.setId(id);
                msg.setFrom(from);
                msg.setGroupId(groupid);
                msg.setGroupName(groupname);
                msg.setReason(reason);
                msg.setTime(time);
                msg.setGroupInviter(groupInviter);

                if (status == InviteMessage.InviteMesageStatus.BEINVITEED.ordinal())
                    msg.setStatus(InviteMessage.InviteMesageStatus.BEINVITEED);
                else if (status == InviteMessage.InviteMesageStatus.BEAGREED.ordinal())
                    msg.setStatus(InviteMessage.InviteMesageStatus.BEAGREED);
                else if (status == InviteMessage.InviteMesageStatus.BEREFUSED.ordinal())
                    msg.setStatus(InviteMessage.InviteMesageStatus.BEREFUSED);
                else if (status == InviteMessage.InviteMesageStatus.AGREED.ordinal())
                    msg.setStatus(InviteMessage.InviteMesageStatus.AGREED);
                else if (status == InviteMessage.InviteMesageStatus.REFUSED.ordinal())
                    msg.setStatus(InviteMessage.InviteMesageStatus.REFUSED);
                else if (status == InviteMessage.InviteMesageStatus.BEAPPLYED.ordinal())
                    msg.setStatus(InviteMessage.InviteMesageStatus.BEAPPLYED);
                else if (status == InviteMessage.InviteMesageStatus.GROUPINVITATION.ordinal())
                    msg.setStatus(InviteMessage.InviteMesageStatus.GROUPINVITATION);
                else if (status == InviteMessage.InviteMesageStatus.GROUPINVITATION_ACCEPTED.ordinal())
                    msg.setStatus(InviteMessage.InviteMesageStatus.GROUPINVITATION_ACCEPTED);
                else if (status == InviteMessage.InviteMesageStatus.GROUPINVITATION_DECLINED.ordinal())
                    msg.setStatus(InviteMessage.InviteMesageStatus.GROUPINVITATION_DECLINED);

                msgs.add(msg);
            }
            cursor.close();
        }
        return msgs;
    }

    /**
     * delete invitation message
     *
     * @param from
     */
    synchronized public void deleteMessage(String from) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.delete(InviteMessgeDao.TABLE_NAME, InviteMessgeDao.COLUMN_NAME_FROM + " = ?", new String[]{from});
        }
    }

    synchronized int getUnreadNotifyCount() {
        int count = 0;
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        if (db.isOpen()) {
            Cursor cursor = db.rawQuery("select " + InviteMessgeDao.COLUMN_NAME_UNREAD_MSG_COUNT + " from " + InviteMessgeDao.TABLE_NAME, null);
            if (cursor.moveToFirst()) {
                count = cursor.getInt(0);
            }
            cursor.close();
        }
        return count;
    }

    synchronized void setUnreadNotifyCount(int count) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            ContentValues values = new ContentValues();
            values.put(InviteMessgeDao.COLUMN_NAME_UNREAD_MSG_COUNT, count);
            db.update(InviteMessgeDao.TABLE_NAME, values, null, null);
        }
    }

    synchronized public void closeDB() {
        if (dbHelper != null) {
            dbHelper.closeDB();
        }
        dbMgr = null;
    }


    /**
     * Save Robot list
     */
    synchronized public void saveRobotList(List<RobotUser> robotList) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.delete(UserDao.ROBOT_TABLE_NAME, null, null);
            for (RobotUser item : robotList) {
                ContentValues values = new ContentValues();
                values.put(UserDao.ROBOT_COLUMN_NAME_ID, item.getUsername());
                if (item.getNick() != null)
                    values.put(UserDao.ROBOT_COLUMN_NAME_NICK, item.getNick());
                if (item.getAvatar() != null)
                    values.put(UserDao.ROBOT_COLUMN_NAME_AVATAR, item.getAvatar());
                db.replace(UserDao.ROBOT_TABLE_NAME, null, values);
            }
        }
    }

    /**
     * load robot list
     */
    synchronized public Map<String, RobotUser> getRobotList() {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Map<String, RobotUser> users = null;
        if (db.isOpen()) {
            Cursor cursor = db.rawQuery("select * from " + UserDao.ROBOT_TABLE_NAME, null);
            if (cursor.getCount() > 0) {
                users = new Hashtable<String, RobotUser>();
            }
            while (cursor.moveToNext()) {
                String username = cursor.getString(cursor.getColumnIndex(UserDao.ROBOT_COLUMN_NAME_ID));
                String nick = cursor.getString(cursor.getColumnIndex(UserDao.ROBOT_COLUMN_NAME_NICK));
                String avatar = cursor.getString(cursor.getColumnIndex(UserDao.ROBOT_COLUMN_NAME_AVATAR));
                RobotUser user = new RobotUser(username);
                user.setNick(nick);
                user.setAvatar(avatar);
                String headerName = null;
                if (!TextUtils.isEmpty(user.getNick())) {
                    headerName = user.getNick();
                } else {
                    headerName = user.getUsername();
                }
                if (Character.isDigit(headerName.charAt(0))) {
                    user.setInitialLetter("#");
                } else {
                    user.setInitialLetter(HanziToPinyin.getInstance().get(headerName.substring(0, 1)).get(0).target
                            .substring(0, 1).toUpperCase());
                    char header = user.getInitialLetter().toLowerCase().charAt(0);
                    if (header < 'a' || header > 'z') {
                        user.setInitialLetter("#");
                    }
                }
                try {
                    users.put(username, user);
                } catch (Exception e) {
                }
            }
            cursor.close();
        }
        return users;
    }
}
